package com.gistone.commis.api.restController;

import com.gistone.commis.constant.Status;
import com.gistone.commis.database.GetBySqlMapper;
import com.gistone.commis.dto.EditorResult;
import com.gistone.commis.dto.FieldError;
import com.gistone.commis.dto.Result;
import com.gistone.commis.entity.Attendance;
import com.gistone.commis.entity.Person;
import com.gistone.commis.entity.User;
import com.gistone.commis.mapper.AttendanceMapper;
import com.gistone.commis.mapper.PersonMapper;
import com.gistone.commis.mapper.UserMapper;
import com.gistone.commis.query.QueryAttendance;
import com.gistone.commis.query.QueryPerson;
import com.gistone.commis.util.DataTablePageUtil;
import com.gistone.commis.util.DateUtil;
import com.gistone.commis.util.ExcelUtil;
import com.gistone.commis.util.ResponseHeaderUtil;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.transaction.Transactional;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.*;

/**
 * 绩效统计
 * Created by wangfan on 2017/5/5.
 */
@RestController("apiPerformanceController")
@RequestMapping("api/performance")
public class PerformanceController {

    private static final Logger logger = LoggerFactory.getLogger(PerformanceController.class);

    @Autowired
    private GetBySqlMapper getBySqlMapper;


    @Transactional
    @RequestMapping(method = RequestMethod.GET, value = "/pageDatatables")
    public DataTablePageUtil<Map>  pageDatatables(HttpServletRequest request,Integer communityId,Date startTime,Date endTime) {
        HttpSession httpSession = request.getSession();
        User user = (User) httpSession.getAttribute("userInfo");
        int userId =user.getUserId();

        String sql ="SELECT att.person_name as person_name,c.community_name as community_name," +
                "       sum(att.leave_affairs) as sum_affairs," +
                "       sum(att.leave_sick) as sum_sick ," +
                "       sum(att.leave_year) as sum_year," +
                "       sum(att.leave_marriage) as sum_marriage," +
                "       sum(att.leave_maternity) as sum_maternity," +
                "       sum(att.leave_funeral) as sum_funeral," +
                "       sum(ot.ot_length) as sum_length," +
                "       sum(ot.ot_type) as sum_type" +
                "       FROM tb_person_att att ,tb_person_overtime ot,tb_community c" +
                "       WHERE  att.person_id =ot.person_id" +
                "       and att.att_year =ot.ot_year" +
                "       and att.att_month =ot.ot_month"+
                "       and c.community_id =att.community_id";
        if(null==startTime){
            if(null!=endTime){
                sql = sql+
                        "       and DATE_FORMAT(att.register_time,'%Y-%m') <= date_format('"+DateUtil.DateToStr2(endTime)+"','%Y-%m') " ;
            }


        }else{
            if(null==endTime) {
                System.out.println("-----"+DateUtil.DateToStr2(startTime));
                sql= sql+ "       and date_format(att.register_time,'%Y-%m') >= date_format('"+DateUtil.DateToStr2(startTime)+"','%Y-%m') " ;
            }else{//都有值
               sql= sql+ "       and date_format(att.register_time,'%Y-%m') >= date_format('"+DateUtil.DateToStr2(startTime)+"','%Y-%m') " +
                        "       and date_format(att.register_time,'%Y-%m') <= date_format('"+DateUtil.DateToStr2(endTime)+"','%Y-%m') ";
            }
        }

        if(null!=communityId){
            sql+="      and att.community_id ="+communityId;
        }

        sql = sql+ "       GROUP BY  att.person_name,c.community_name";







        DataTablePageUtil<Map> dataTable = new DataTablePageUtil<>(request);
        PageHelper.startPage(dataTable.getPage_num(), 10);
        List<Map> list =		this.getBySqlMapper.findRecords(sql);
        PageInfo<Map> pageInfo =new PageInfo<>(list);
        System.out.println(list.size());
        dataTable.setDraw(dataTable.getDraw());
        dataTable.setData(pageInfo.getList());
        dataTable.setRecordsTotal((int)pageInfo.getTotal());
        dataTable.setRecordsFiltered(dataTable.getRecordsTotal());
        //返回数据到页面

        return dataTable;

    }




    @RequestMapping(method = RequestMethod.GET, value = "/export")
    public void export(HttpServletResponse response,Integer communityId,Date startTime,Date endTime) throws IOException {
        String fileName = "绩效考勤"+DateUtil.DateToStr2(new Date())+".xls";
        String sheetName = "绩效考勤";//sheet名
        String []title = new String[]{"人员姓名","所属社区","事假","病假","年假","产假","婚假","丧假","普通加班","假期加班"};//标题

        String sql ="SELECT att.person_name as person_name,c.community_name as community_name," +
                "       sum(att.leave_affairs) as sum_affairs," +
                "       sum(att.leave_sick) as sum_sick ," +
                "       sum(att.leave_year) as sum_year," +
                "       sum(att.leave_marriage) as sum_marriage," +
                "       sum(att.leave_maternity) as sum_maternity," +
                "       sum(att.leave_funeral) as sum_funeral," +
                "       sum(ot.ot_length) as sum_length," +
                "       sum(ot.ot_type) as sum_type" +
                "       FROM tb_person_att att ,tb_person_overtime ot,tb_community c" +
                "       WHERE  att.person_id =ot.person_id" +
                "       and att.att_year =ot.ot_year" +
                "       and att.att_month =ot.ot_month"+
                "       and c.community_id =att.community_id";
        if(null==startTime){
            if(null!=endTime){
                sql = sql+
                        "       and DATE_FORMAT(att.register_time,'%Y-%m') <= date_format('"+DateUtil.DateToStr2(endTime)+"','%Y-%m') " ;
            }


        }else{
            if(null==endTime) {
                System.out.println("-----"+DateUtil.DateToStr2(startTime));
                sql= sql+ "       and date_format(att.register_time,'%Y-%m') >= date_format('"+DateUtil.DateToStr2(startTime)+"','%Y-%m') " ;
            }else{//都有值
                sql= sql+ "       and date_format(att.register_time,'%Y-%m') >= date_format('"+DateUtil.DateToStr2(startTime)+"','%Y-%m') " +
                        "       and date_format(att.register_time,'%Y-%m') <= date_format('"+DateUtil.DateToStr2(endTime)+"','%Y-%m') ";
            }
        }

        if(null!=communityId){
            sql+="      and att.community_id ="+communityId;
        }

        sql = sql+ "       GROUP BY  att.person_name,c.community_name";


        List<Map> list =		this.getBySqlMapper.findRecords(sql);


        String [][]values = new String[list.size()][];
        for(int i=0;i<list.size();i++){
            values[i] = new String[title.length];
            //将对象内容转换成string
            Map item = list.get(i);
            values[i][0] = item.get("person_name")+"";
            values[i][1] = item.get("community_name")+"";
            values[i][2] = item.get("sum_affairs")+"";
            values[i][3] = item.get("sum_sick")+"";
            values[i][4] = item.get("sum_year")+"";
            values[i][5] = item.get("sum_maternity")+"";
            values[i][6] = item.get("sum_marriage")+"";
            values[i][7] = item.get("sum_funeral")+"";
            values[i][8] = item.get("sum_length")+"";
            values[i][9] = item.get("sum_type")+"";

        }

        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, values, null);

        //将文件存到指定位置
        try {
            ResponseHeaderUtil.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
//        return JResponse.success("ok");
//
//        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
//        FileCopyUtils.copy(inputStream, response.getOutputStream());
    }



}
